package com.yunysha.contentmodel;

import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;


import net.sf.json.JSONArray;
import antlr.collections.impl.Vector;

import com.yunysha.DBManager.DBManager;
import com.yunysha.beans.EntityBean;
import com.yunysha.beans.ModuleBean;
import com.yunysha.beans.UserBean;
import com.yunysha.struts.actionform.UserMasterForm;
import java.lang.Object;


public class YunyshaCM {

	private DBManager db;
	
	public YunyshaCM(){
	}
	
	public YunyshaCM(DBManager db){
		this.db = db;
	}
	
	public UserBean validateUserLoginCM(String pUserId, String pUserPwd){
		
		UserBean uBean = null;
		
		PreparedStatement ps = null;
		ResultSet rs= null;
		
		try{
			String sqlQuery = "SELECT userId,CONCAT(userFirstName,' ',userLastName),userMobile,userEmail,userLoginId " +
					" FROM user_master WHERE userLoginId=? AND userPassword=? AND rowstate!=-1";
			
			ps = db.getPreparedStatement(sqlQuery);
			
			ps.setString(1, pUserId);
			ps.setString(2, pUserPwd);
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				uBean = new UserBean();
				
				uBean.setUserId(rs.getInt(1));
				uBean.setUserName(rs.getString(2));
				uBean.setUserMobile(rs.getString(3));
				uBean.setUserEmail(rs.getString(4));
				uBean.setUserLoginId(rs.getString(5));				
			}
			
		} catch(Exception ex){
			ex.printStackTrace();
		} finally{
			try{
				if(rs!=null)
					rs.close();
				if(ps!=null)
					ps.close();
			} catch(Exception ex){
				ex.printStackTrace();
			}
		}
		
		return uBean;
		
	}
	
	public ArrayList<ModuleBean> retreiveModuleListCM(int pUserId){
		
		ArrayList<ModuleBean> moduleList = new ArrayList<ModuleBean>();
		ArrayList<EntityBean> entityList = new ArrayList<EntityBean>();
		ModuleBean moduleBean = null;
		EntityBean entityBean = null;
		
		PreparedStatement ps = null;
		ResultSet rs= null;
		
		int moduleId = 0;
		try{
			String moduleQuery = "SELECT DISTINCT moduleId, moduleName, moduleAction,entityId,entityName,entityAction FROM module_master mm JOIN entity_master em ON em.parentModuleId = mm.moduleId " +
								" JOIN permissions_master pm ON mm.moduleId=pm.parentModuleId AND pm.parentEntityId=em.entityId "+
								" JOIN usergroup_permission_mapping upm ON pm.permissionId = upm.permissionId "+
								" JOIN user_usergroup_mapping uum ON uum.usergroupId = upm.usergroupId "+
								" WHERE uum.userId = ? AND mm.rowstate!=-1 AND pm.rowstate!=-1 " +
								" AND uum.rowstate!=-1 AND upm.rowstate!=-1 AND em.rowstate!=-1";
			
			
			ps = db.getPreparedStatement(moduleQuery);
			
			ps.setInt(1, pUserId);
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				
				if(moduleId == rs.getInt(1)){
					entityBean = new EntityBean();
					
					entityBean.setEntityId(rs.getInt(4));
					entityBean.setEntityName(rs.getString(5));
					entityBean.setEntityAction(rs.getString(6));
					
					entityList.add(entityBean);
				} else {
					if(moduleBean!=null){
						moduleBean.setEntityList(entityList);
						moduleList.add(moduleBean);
						moduleBean = null;
					}
					
					moduleId = rs.getInt(1);
					
					entityList = new ArrayList<EntityBean>();
					
					moduleBean = new ModuleBean();
					
					moduleBean.setModuleId(moduleId);
					moduleBean.setModuleName(rs.getString(2));
					moduleBean.setModuleAction(rs.getString(3));
					
					entityBean = new EntityBean();
					
					entityBean.setEntityId(rs.getInt(4));
					entityBean.setEntityName(rs.getString(5));
					entityBean.setEntityAction(rs.getString(6));
					
					entityList.add(entityBean);
					
				}
			}
			
			if(moduleBean!=null){
				moduleBean.setEntityList(entityList);
				moduleList.add(moduleBean);
			}
			
		} catch(Exception ex){
			ex.printStackTrace();
		} finally{
			try{
				if(rs!=null)
					rs.close();
				if(ps!=null)
					ps.close();
			} catch(Exception ex){
				ex.printStackTrace();
			}
		}
		
		return moduleList;
	}
	
	public void retreiveUserDetailsCM(String pUserId, UserMasterForm pUserForm){
		
		PreparedStatement ps = null;
		ResultSet rs= null;
		
		try{
			String sqlQuery = "SELECT userId,userFirstName,userMiddleName, userLastName,userMobile,userEmail,userLoginId " +
					" FROM user_master WHERE userId=? AND rowstate!=-1";
			
			ps = db.getPreparedStatement(sqlQuery);
			
			ps.setString(1, pUserId);
			
			rs = ps.executeQuery();
			
			while(rs.next()){				
				
				pUserForm.setUserId(rs.getInt(1));
				pUserForm.setUserFirstName(rs.getString(2));
				pUserForm.setUserMiddleName(rs.getString(3));
				pUserForm.setUserLastName(rs.getString(4));
				pUserForm.setUserMobile(rs.getString(5));
				pUserForm.setUserEmail(rs.getString(6));
				pUserForm.setUserLoginId(rs.getString(7));				
			}
			
		} catch(Exception ex){
			ex.printStackTrace();
		} finally{
			try{
				if(rs!=null)
					rs.close();
				if(ps!=null)
					ps.close();
			} catch(Exception ex){
				ex.printStackTrace();
			}
		}		
	}
	
	public ArrayList<UserBean> retrieveAllUsersCM(String pSearchUserName, String pUserMobile,
			String pUserEmail, int pageOffSet){
		
		UserBean uBean = null;
		ArrayList<UserBean> uList = new ArrayList<UserBean>();
		
		PreparedStatement ps = null;
		ResultSet rs= null;
		
		int cnt=1;
		try{
			StringBuilder sqlQuery = new StringBuilder("SELECT userId,CONCAT(userFirstName,' ',userLastName),userMobile,userEmail,userLoginId " +
					" FROM user_master WHERE rowstate!=-1 ");
			
			if(pSearchUserName!=null && !"".equals(pSearchUserName)){
				sqlQuery.append(" AND (userFirstName like ? OR userLastName like ?) ");
			}
			if(pUserMobile!=null && !"".equals(pUserMobile)){
				sqlQuery.append(" AND userMobile like ? ");
			}
			if(pUserEmail!=null && !"".equals(pUserEmail)){
				sqlQuery.append(" AND userEmail like ? ");
			}
			sqlQuery.append(" LIMIT ?, 10");
			
			ps = db.getPreparedStatement(sqlQuery.toString());
			
			if(pSearchUserName!=null && !"".equals(pSearchUserName)){
				ps.setString(cnt++, "%"+pSearchUserName+"%");
				ps.setString(cnt++, "%"+pSearchUserName+"%");
			}
			if(pUserMobile!=null && !"".equals(pUserMobile)){
				ps.setString(cnt++, "%"+pUserMobile+"%");
			}
			if(pUserEmail!=null && !"".equals(pUserEmail)){
				ps.setString(cnt++, "%"+pUserEmail+"%");
			}
			
			ps.setInt(cnt++, pageOffSet);
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				uBean = new UserBean();
				
				uBean.setUserId(rs.getInt(1));
				uBean.setUserName(rs.getString(2));
				uBean.setUserMobile(rs.getString(3));
				uBean.setUserEmail(rs.getString(4));
				uBean.setUserLoginId(rs.getString(5));	
				
				uList.add(uBean);
			}
			
		} catch(Exception ex){
			ex.printStackTrace();
		} finally{
			try{
				if(rs!=null)
					rs.close();
				if(ps!=null)
					ps.close();
			} catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return uList;
	}
	
	public JSONArray retrieveUsersSearchCM(String pSearchUserName, String pUserMobile,
			String pUserEmail, int pageOffSet){
		
		JSONArray jArray = new JSONArray();
		
		
		PreparedStatement ps = null;
		ResultSet rs= null;
		ArrayList<String> aList = null;
        int cnt=1;
		try{
			
			StringBuilder sqlQuery = new StringBuilder("SELECT CONCAT(userFirstName,' ',userLastName),userMobile,userEmail,userLoginId,userPassword " +
					" FROM user_master WHERE rowstate!=-1 ");
			
			if(pSearchUserName!=null && !"".equals(pSearchUserName)){
				sqlQuery.append(" AND (userFirstName like ? OR userLastName like ?) ");
			}
			if(pUserMobile!=null && !"".equals(pUserMobile)){
				sqlQuery.append(" AND userMobile like ? ");
			}
			if(pUserEmail!=null && !"".equals(pUserEmail)){
				sqlQuery.append(" AND userEmail like ? ");
			}
//			sqlQuery.append(" LIMIT ?, 10");
			
			ps = db.getPreparedStatement(sqlQuery.toString());
			
			if(pSearchUserName!=null && !"".equals(pSearchUserName)){
				ps.setString(cnt++, "%"+pSearchUserName+"%");
				ps.setString(cnt++, "%"+pSearchUserName+"%");
			}
			if(pUserMobile!=null && !"".equals(pUserMobile)){
				ps.setString(cnt++, "%"+pUserMobile+"%");
			}
			if(pUserEmail!=null && !"".equals(pUserEmail)){
				ps.setString(cnt++, "%"+pUserEmail+"%");
			}
			
			System.out.println(sqlQuery);
//			ps.setInt(cnt++, pageOffSet);
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				aList = new ArrayList<String>();
				for(int i=1;i<=5;i++){
					aList.add(rs.getString(i));
				}
				jArray.add(aList);
			}			
		} catch(Exception ex){
			ex.printStackTrace();
		} finally{
			try{
				if(rs!=null)
					rs.close();
				if(ps!=null)
					ps.close();
			} catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return jArray;
	}
	
	public JSONArray retrieveData(String selectClause, String table){
		
		JSONArray jArray = new JSONArray();
		
		
		PreparedStatement ps = null;
		ResultSet rs= null;
		ArrayList<String> aList = null;
        int cnt=1;
		try{
			
			StringBuilder sqlQuery = new StringBuilder("SELECT "+selectClause+" from "+table+" WHERE rowstate!=-1 ");
			
			
			ps = db.getPreparedStatement(sqlQuery.toString());
			
			
			System.out.println(sqlQuery);
			System.out.println("retrive data");
//			ps.setInt(cnt++, pageOffSet);
			
			rs = ps.executeQuery();
			
			while(rs.next()){
				aList = new ArrayList<String>();
				for(int i=1;i<=5;i++){
					aList.add(rs.getString(i));
				}
				jArray.add(aList);
			}			
		} catch(Exception ex){
			ex.printStackTrace();
		} finally{
			try{
				if(rs!=null)
					rs.close();
				if(ps!=null)
					ps.close();
			} catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return jArray;
	}
	
	
	
	public boolean insertOrUpdateUserDetailsCM(UserMasterForm pUserForm){
		PreparedStatement ps = null;
		ResultSet rs= null;
		
		boolean isSuccess=false;
		try{
			
			String sqlQuery = "";
			
			if(pUserForm.getUserId()==0){
				sqlQuery = "INSERT INTO user_master (userFirstName,userMiddleName,userLastName,userMobile,userEmail,userLoginId,userPassword,rowstate )" +
						" values (?,?,?,?,?,?,?,?)" ;
			} else {
				sqlQuery = "UPDATE user_master set userFirstName=?,userMiddleName=?,userLastName=?,userMobile=?," +
						" userEmail=?,userLoginId=?,userPassword=?,rowstate=rowstate+1 WHERE userId = ? AND rowstate>-1";
			}
					
			
			ps = db.getPreparedStatement(sqlQuery);
			
			ps.setString(1,pUserForm.getUserFirstName());
			ps.setString(2,pUserForm.getUserMiddleName());
			ps.setString(3,pUserForm.getUserLastName());
			ps.setString(4,pUserForm.getUserMobile());
			ps.setString(5,pUserForm.getUserEmail());
			ps.setString(6,pUserForm.getUserLoginId());
			ps.setString(7,pUserForm.getUserLoginId());
			if(pUserForm.getUserId()==0){
				ps.setInt(8, 0);
			} else {
				ps.setInt(8,pUserForm.getUserId());
			}
			
			int updated = ps.executeUpdate();
			
			if(updated>0){
				isSuccess = true;
			}
						
		} catch(Exception ex){
			ex.printStackTrace();
		} finally{
			try{
				if(rs!=null)
					rs.close();
				if(ps!=null)
					ps.close();
			} catch(Exception ex){
				ex.printStackTrace();
			}
		}
		
		return isSuccess;
	}
	
	
	public boolean saveCompanyMaster(HashMap<String,String> dataMap){
		PreparedStatement ps = null;
		ResultSet rs= null;
		int entity_id_cnt=0;
		int entity_id=0;
		
		boolean isSuccess=false;
		try{
			System.out.println("in cm");
			String sqlQuery = "";
			String insertKey="";
			String insertValue="";
			
			if(dataMap.get("txtCode")!=null && dataMap.get("txtCode").equals("")){
				
				
				Iterator entries = dataMap.entrySet().iterator();
				while (entries.hasNext()) {
				    Map.Entry entry = (Map.Entry) entries.next();
				    String key = entry.getKey().toString();
				    String value = entry.getValue().toString();
				    System.out.println("Key = " + key + ", Value = " + value);
				    insertKey=insertKey+key+",";
				    insertValue=insertValue+"'"+value+"',";
				    System.out.println(insertKey);
				    System.out.println(insertValue);
				}
				
				insertKey=insertKey.substring(0,insertKey.length()-1);
				insertValue=insertValue.substring(0,insertValue.length()-1);
				sqlQuery = "INSERT INTO companyMaster ("+insertKey+",rowstate )" +
						" values ("+insertValue+",'0')" ;
				System.out.println(sqlQuery);
			/*} else {
				sqlQuery = "UPDATE user_master set userFirstName=?,userMiddleName=?,userLastName=?,userMobile=?," +
						" userEmail=?,userLoginId=?,userPassword=?,rowstate=rowstate+1 WHERE userId = ? AND rowstate>-1";
			}*/

			
			ps = db.getPreparedStatement(sqlQuery);
						
		   ps.execute(); 
		   
		   ps=null;
		   rs=null;
		   
		   
		   String sqlQuery3="select max(entity_id) from companyMaster ";
		   System.out.println(sqlQuery3);
		   ps = db.getPreparedStatement(sqlQuery3);
		   rs = ps.executeQuery();
			
			while(rs.next()){
					entity_id=Integer.parseInt(rs.getString(1));
				}
		   
		   String zone=dataMap.get("txtCZone");
		   String sqlQuery1="select count(entity_id) from companyMaster where txtCZone='"+zone+"' and rowstate>-1";
		   System.out.println(sqlQuery1);
		   ps = db.getPreparedStatement(sqlQuery1);
		   rs = ps.executeQuery();
			
			while(rs.next()){
					entity_id_cnt=Integer.parseInt(rs.getString(1));
				}
			
			String code="";
			if(zone.equalsIgnoreCase("North"))
			{code="NR"+entity_id_cnt;}

			if(zone.equalsIgnoreCase("south"))
			{code="SR"+entity_id_cnt;}

			if(zone.equalsIgnoreCase("east"))
			{code="ER"+entity_id_cnt;}

			if(zone.equalsIgnoreCase("west"))
			{code="WR"+entity_id_cnt;}
			
			ps=null;
			rs=null;
			  String sqlQuery2="update companyMaster set txtCode='"+code+"' where entity_id="+entity_id;
			  System.out.println(sqlQuery2);
			   ps = db.getPreparedStatement(sqlQuery2);
			    ps.executeUpdate();
			
			}
			
									
		} catch(Exception ex){
			ex.printStackTrace();
		} finally{
			try{
				if(rs!=null)
					rs.close();
				if(ps!=null)
					ps.close();
			} catch(Exception ex){
				ex.printStackTrace();
			}
		}
		
		return isSuccess;
	}

	public boolean saveDesignMaster(HashMap<String,String> dataMap){
		PreparedStatement ps = null;
		ResultSet rs= null;
		int entity_id_cnt=0;
		int entity_id=0;
		
		boolean isSuccess=false;
		try{
			System.out.println("in cm");
			String sqlQuery = "";
			String insertKey="";
			String insertValue="";
			
			
				
				
				Iterator entries = dataMap.entrySet().iterator();
				while (entries.hasNext()) {
				    Map.Entry entry = (Map.Entry) entries.next();
				    String key = entry.getKey().toString();
				    String value = entry.getValue().toString();
				    System.out.println("Key = " + key + ", Value = " + value);
				    insertKey=insertKey+key+",";
				    insertValue=insertValue+"'"+value+"',";
				    System.out.println(insertKey);
				    System.out.println(insertValue);
				}
				
				insertKey=insertKey.substring(0,insertKey.length()-1);
				insertValue=insertValue.substring(0,insertValue.length()-1);
				sqlQuery = "INSERT INTO designMaster ("+insertKey+",rowstate,logged_date )" +
						" values ("+insertValue+",'0',now())" ;
				System.out.println(sqlQuery);
			/*} else {
				sqlQuery = "UPDATE user_master set userFirstName=?,userMiddleName=?,userLastName=?,userMobile=?," +
						" userEmail=?,userLoginId=?,userPassword=?,rowstate=rowstate+1 WHERE userId = ? AND rowstate>-1";
			}*/

			
			ps = db.getPreparedStatement(sqlQuery);
						
		   ps.execute(); 
		   
		   ps=null;
		   rs=null;
		   
		   
		   String sqlQuery3="select max(entity_id) from designMaster ";
		   System.out.println(sqlQuery3);
		   ps = db.getPreparedStatement(sqlQuery3);
		   rs = ps.executeQuery();
			
			while(rs.next()){
					entity_id=Integer.parseInt(rs.getString(1));
				}
		   
		   			
			ps=null;
			rs=null;
			String codeGen=dataMap.get("txtMetalCategory");
			System.out.println("codeGen::"+codeGen);
			  String sqlQuery2="update designMaster set designCode='"+codeGen+entity_id+"' where entity_id="+entity_id;
			  System.out.println(sqlQuery2);
			   ps = db.getPreparedStatement(sqlQuery2);
			    ps.executeUpdate();
			
			
			
									
		} catch(Exception ex){
			ex.printStackTrace();
		} finally{
			try{
				if(rs!=null)
					rs.close();
				if(ps!=null)
					ps.close();
			} catch(Exception ex){
				ex.printStackTrace();
			}
		}
		
		return isSuccess;
	}
	
	/*public String getDependentData(String strTableName, String strMasterValue,
			String strSelectFields, String strWhereFields, String strHTMLName) {
 		ResultSet dataSet = null;

		Writer writer = null;
		writer = new java.io.StringWriter();
		XMLWriter xmlwriter = new XMLWriter(writer);

		String strDependentDataXML = "";

		String strFieldsArray[] = strSelectFields.split("@");
		String strFields = "";
		for (int i = 0; i < strFieldsArray.length; i++) {
			strFields += strFieldsArray[i] + ",";
		}
		strFields = strFields.substring(0, ((strFields.length()) - 1));

		Vector rows = null;

		try {
			xmlwriter.writeEntity (XML_RESPONSE);
			xmlwriter.writeAttribute("fieldhtmlname", strHTMLName);
			StringBuffer query = new StringBuffer();
			query.append("SELECT " + strFields);
			query.append(" FROM "+ strTableName);
			query.append(" WHERE " + strWhereFields + "='" + strMasterValue
					+ "' and rowstate>-1");

			dataSet = db.getDataSetFromSQL(query.toString());
			rows = dataSet.getRows();
			Vector row;
			if ((rows != null) && (rows.size() > 0)) {
				for (int intRowsCount = 0; intRowsCount < rows.size(); intRowsCount++) {
					row = (Vector) rows.elementAt(intRowsCount);
					xmlwriter.writeEntity("XML_ROW");
					for (int intFieldCount = 0; intFieldCount < strFieldsArray.length; intFieldCount++) {
						// strFieldName = strFieldsArray[intFieldCount];
						// strFieldValue =
						// row.elementAt(intFieldCount).toString();

						xmlwriter.writeEntity("XML_FIELD");
						xmlwriter.writeAttribute("XML_FIELDNAME",
								strFieldsArray[intFieldCount]);
						xmlwriter.writeText(row.elementAt(intFieldCount)
								.toString());
						xmlwriter.endEntity();
					}
					xmlwriter.endEntity();
				}
			}
			xmlwriter.endEntity();

			strDependentDataXML = writer.toString();
			// // //LOGGER.info("Response Dependent XML ::" +
			// strDependentDataXML);
		} catch (Exception exc) {
			// exc.printStackTrace();
			
		} finally {
			try {
				if (xmlwriter != null) {
					xmlwriter.close();
				}
				if (writer != null) {
					writer.close();
				}
				dataSet = null;
				rows = null;
			} catch (Exception e) {
				
			}
		}
		return strDependentDataXML;
	}// method getDependentData() ends here
	
	
*/	
}
